package com.limao.DBProvider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.limao.DBDataClass.SysScreenAdvDataClass;
import com.limao.DbConnection.DbConnectionManager;

public class SysScreenAdvProvider {

	private static SysScreenAdvProvider instance;

	public static SysScreenAdvProvider getInstance() {
		if(instance==null) instance=new SysScreenAdvProvider();
		return instance;
	}
	
	 public ArrayList<SysScreenAdvDataClass> Load_SyScreen_Adv(int vip)
	 {
		 ArrayList<SysScreenAdvDataClass> list = new ArrayList<SysScreenAdvDataClass>();
			SysScreenAdvDataClass tmp = null;
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("select * from cd_sys_screenadv where state=1 and vip=? order by sort desc ");
				pstmt.setInt(1, vip);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					tmp = new SysScreenAdvDataClass();
					tmp.advid= rs.getString("advid");
					tmp.name= rs.getString("name");
					tmp.vip= rs.getString("vip");
					tmp.state= rs.getString("state");
					tmp.sort= rs.getString("sort");
					tmp.image= rs.getString("image");
					tmp.num= rs.getString("num");
					tmp.url= rs.getString("url");
					tmp.txt= rs.getString("txt");
					tmp.createtime= rs.getString("createtime");
					tmp.updatetime= rs.getString("updatetime");

					list.add(tmp);
				}

			} catch (Exception e) {
				list .clear();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
			return list;
	 }
	 
	 //更新点击量
	 public void Update_SyScreen_Adv_Num(int vip)
	 {
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("update cd_sys_screenadv set num=num+1 where state=1 and vip=? ");
				pstmt.setInt(1, vip);
				pstmt.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}

	 }
	
}
